<?php
	session_start();
	ob_start();

	require("./inc/config-system.inc.php");
	require("./inc/config-db.inc.php");
	require("./inc/fm.inc.php");
	require("./class/adodb/adodb.inc.php");

	// connection database
	$objConn = ADONewConnection(DB_DRIVER);
	$objConn->Connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
	//$objConn->debug = TRUE;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>AR Form Document Posting</title>
<style type="text/css">
<!--
.center {
	text-align: center;
	font-weight: normal;
	font-size: 14px;
	font-family: "Microsoft Sans Serif";
}
.right {
	text-align: right;
}
.body {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
	margin-top: 0px;
}
.input {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
}
.header_center {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
	text-decoration: underline;
	text-align: center;
	font-weight: bold;
	color: #FFF;
	background-color: #000;
}
.input_disable {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
	color: #000;
	background-color: #CCC;
}
.input_focus {
	font-family: "Microsoft Sans Serif";
	font-size: 14px;
	background-color: #FFC;
	border: 2px solid #F60;
}
.top {
	-moz-border-radius-bottomleft:0.5em;
	-moz-border-radius-bottomright:0.5em;
	-moz-border-radius-topleft:0.5em;
	-moz-border-radius-topright:0.5em;
	background-color: #000;
	border:2px solid #F90;
	color: #006;
	display:block;
	overflow:auto;
	padding:6px 12px;
	white-space:pre;
}

-->
</style>

<link href="./css/datepicker.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/tablesorter.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/tablehover.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/slidepanel.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/autocomplete.css" rel="stylesheet" type="text/css" media="screen" />

<script type="text/javascript" src="./js/jquery.inc.js"></script>
<script type="text/javascript" src="./js/form.inc.js"></script>
<script type="text/javascript" src="./js/datepicker.inc.js"></script>
<script type="text/javascript" src="./js/tablesorter.inc.js"></script>
<script type="text/javascript" src="./js/tablehover.inc.js"></script>
<script type="text/javascript" src="./js/livequery.inc.js"></script>
<script type="text/javascript" src="./js/autocomplete.inc.js"></script>
<script type="text/javascript" src="./js/floatingbox.inc.js"></script>

<?php require("./gl.inc.js.php"); ?>
</head>

<body class="body">

<div id="mainPanel">
    <div id="panel"><?php require("./menu.inc.php"); ?></div>
    <p class="slide"><a href="#" class="btn-slide">Main Menu</a></p>
</div>

<div class="top" id="top"><a href="#inputbox">Go to TOP</a></div>

<br /><br />

<?php
	// get filter from user input
	if (isset($_POST["TXTgl_account"])) {
		$gl_account = $_POST["TXTgl_account"];
	} else {
		$gl_account = "";
	}

	if (isset($_POST["TXTdoc_date_from"])) {
		$doc_date_from = $_POST["TXTdoc_date_from"];
	} else {
		$doc_date_from = "";
	}

	if (isset($_POST["TXTdoc_date_to"])) {
		$doc_date_to = $_POST["TXTdoc_date_to"];
	} else {
		$doc_date_to = "";
	}
?>
<form id="gl_display" method="post" action="./gl_display.php">
    <table width="100%" border="0" cellpadding="2" cellspacing="2" class="body">
        <tr class="header_center">
            <td><a name="inputbox">GL Seletion Filter</a></td>
        </tr>
        <tr>
            <td class="left">&nbsp;</td>
        </tr>
        <tr>
            <td>GL Account :<br />
			<input name="TXTgl_account" type="text" class="input" id="TXTgl_account" value="<?php echo $gl_account; ?>" /></td>
		</tr>
        <tr>
            <td>Document Date :<br />
            <input name="TXTdoc_date_from" type="text" class="input" id="TXTdoc_date_from" value="<?php echo $doc_date_from; ?>" readonly="readonly" />
            To&nbsp;
            <input name="TXTdoc_date_to" type="text" class="input" id="TXTdoc_date_to" value="<?php echo $doc_date_to; ?>" readonly="readonly" /></td>
		</tr>
        <tr>
            <td><input type="submit" name="BTTexecute" id="BTTexecute" value="Execute" class="input" />
                <input type="reset" name="BTTreset" id="BTTreset" value="Reset" class="input" />
            </td>
        </tr>
</table>
</form>

<?php if (isset($_POST["BTTexecute"])) { ?>

	<?php
		// replace * to % for use in SQL
		if ($gl_account == "") {
			$gl_account = "%";
		} else {
			$gl_account = str_replace("*", "%", $gl_account);
		}

		if ($doc_date_from == "") {
			$doc_date_from = "0000-00-00";
		} else {
			$doc_date_from = str_replace("*", "%", $doc_date_from);
		}

		if ($doc_date_to == "") {
			$doc_date_to = "9999-12-31";
		} else {
			$doc_date_to = str_replace("*", "%", $doc_date_to);
		}

		// keep AR data into result array
		$result_data = array();
		$gl_account_temp = "";

		$sqlCommand = "SELECT * FROM tbl_ar WHERE ar_account LIKE '" . $gl_account . "' AND (ar_docdate BETWEEN '" . $doc_date_from . "' AND '" . $doc_date_to . "') ORDER BY ar_account";
		$recordset = $objConn->Execute($sqlCommand);

		while (!$recordset->EOF) {
			$result_data_temp["gl_account"] = $recordset->fields["ar_account"];
			$result_data_temp["pk"] = $recordset->fields["ar_postkey"];
			$result_data_temp["docno"] = "AR" . str_pad($recordset->fields["ar_docno"], 8, 0, STR_PAD_LEFT);
			$result_data_temp["docdate"] = $recordset->fields["ar_docdate"];
			if ($recordset->fields["ar_ref_ap"] != "") {
				$result_data_temp["refap"] = "AP" . str_pad($recordset->fields["ar_ref_ap"], 8, 0, STR_PAD_LEFT);
			} else {
				$result_data_temp["refap"] = "";
			}
			$result_data_temp["description"] = $recordset->fields["ar_description"];
			$result_data_temp["quantity"] = $recordset->fields["ar_quantity"];
			$result_data_temp["price_unit"] = $recordset->fields["ar_price_unit"];
			$result_data_temp["amount"] = $recordset->fields["ar_amount"];

			array_push($result_data, $result_data_temp);
			$recordset->MoveNext();
		}
		unset($result_data_temp); // clear array

		// keep AP data into result array
		$sqlCommand = "SELECT * FROM tbl_ap WHERE ap_account LIKE '" . $gl_account . "' AND (ap_docdate BETWEEN '" . $doc_date_from . "' AND '" . $doc_date_to . "') ORDER BY ap_account";
		$recordset = $objConn->Execute($sqlCommand);

		while (!$recordset->EOF) {
			$result_data_temp["gl_account"] = $recordset->fields["ap_account"];
			$result_data_temp["pk"] = $recordset->fields["ap_postkey"];
			$result_data_temp["docno"] = "AP" . str_pad($recordset->fields["ap_docno"], 8, 0, STR_PAD_LEFT);
			$result_data_temp["docdate"] = $recordset->fields["ap_docdate"];
			$result_data_temp["refap"] = "";
			$result_data_temp["description"] = $recordset->fields["ap_description"];
			$result_data_temp["quantity"] = $recordset->fields["ap_quantity"];
			$result_data_temp["price_unit"] = $recordset->fields["ap_price_unit"];
			$result_data_temp["amount"] = $recordset->fields["ap_amount"];

			array_push($result_data, $result_data_temp);
			$recordset->MoveNext();
		}
		unset($result_data_temp); // clear array

		// keep ADJUST data into result array
		$sqlCommand = "SELECT * FROM tbl_adj WHERE adj_account LIKE '" . $gl_account . "' AND (adj_docdate BETWEEN '" . $doc_date_from . "' AND '" . $doc_date_to . "') ORDER BY adj_account";
		$recordset = $objConn->Execute($sqlCommand);

		while (!$recordset->EOF) {
			$result_data_temp["gl_account"] = $recordset->fields["adj_account"];
			$result_data_temp["pk"] = $recordset->fields["adj_postkey"];
			$result_data_temp["docno"] = "AD" . str_pad($recordset->fields["adj_docno"], 8, 0, STR_PAD_LEFT);
			$result_data_temp["docdate"] = $recordset->fields["adj_docdate"];
			$result_data_temp["refap"] = "";
			$result_data_temp["description"] = $recordset->fields["adj_description"];
			$result_data_temp["quantity"] = $recordset->fields["adj_quantity"];
			$result_data_temp["price_unit"] = $recordset->fields["adj_price_unit"];
			$result_data_temp["amount"] = $recordset->fields["adj_amount"];

			array_push($result_data, $result_data_temp);
			$recordset->MoveNext();
		}
		unset($result_data_temp); // clear array

		// sort result array by value
		asort($result_data);
	?>

 	<table width="100%">
        <tr class="header_center">
            <td colspan="7">GL Display Result (Break by Account)</td>
        </tr>
    </table>

<table id="mainTable" width="100%">
	<tr><td>

<?php 	$sum_sub_total = 0.00;
		$sign_string_amount = "";

		while (list($key, $value) = each($result_data)) { // !!! do not use foreach() !!!

			if ($value["gl_account"] != $gl_account_temp) {
	?>
<table id="myTable" name="nameTable" class="tablesorter" width="100%" border="0" cellpadding="2" cellspacing="2">
				<thead class="input_disable">
                	<tr>
					<th width="13%">Account</th>
					<th width="5%">Doc. No.</th>
					<th width="8%">Doc. Date</th>
					<th width="7%">Ref. AP</th>
					<th width="35%">Description</th>
                    <th width="7%">Quantity</th>
                    <th width="9%">Price / Unit</th>
					<th colspan="2">Amount</th>
                    </tr>
				</thead>
<tbody>
    <?php
				$gl_account_temp = $value["gl_account"];
				$sum_sub_total = 0.00;
			}

			// GL 1 or 2 or 3 or 4 or 5
			if (substr($value["gl_account"], 0, 1) == "1" ||
				substr($value["gl_account"], 0, 1) == "2" ||
				substr($value["gl_account"], 0, 1) == "3" ||
				substr($value["gl_account"], 0, 1) == "4" ||
				substr($value["gl_account"], 0, 1) == "5") {

				if ($value["pk"] == "01") {
					$sign_string_amount = "<font color=green>" . number_format($value["amount"], 2) . "</font>";
				} elseif ($value["pk"] == "02") {
					$value["amount"] = $value["amount"] * -1;
					$sign_string_amount = "<font color=red>" . shiftLeftAndRoate($value["amount"]) . "</font>"; // show sign minus SAP format
				}
			}

			$sum_sub_total = $sum_sub_total + $value["amount"];
	?>
       <tr>
            <td><?php echo $value["gl_account"]; ?></td>
            <td><?php echo $value["docno"]; ?></td>
            <td><?php echo $value["docdate"]; ?></td>
			<td><?php echo $value["refap"]; ?></th>
            <td><?php echo $value["description"]; ?></td>
            <td><?php echo number_format($value["quantity"], 2); ?></td>
            <td><?php echo number_format($value["price_unit"], 2); ?></td>
            <td width="16%"><?php echo $sign_string_amount; ?></td>
    </tr>

  	<?php
			// read next record array for check end of gl account
  			$next_record = current($result_data); // ห้ามใช้ next() เพราะว่า cursor เลื่อนมาที่แถวต่อไปแล้วใช้ current() ดึงออกมาได้เลย

  			if ($next_record["gl_account"] != $value["gl_account"]) {
  	?>
</tbody></table>

<table width="100%">
                <tr>
                    <td width="150">&nbsp;</td>
                    <td width="67">&nbsp;</td>
                    <td width="90">&nbsp;</td>
                    <td width="78">&nbsp;</td>
                    <td width="410">&nbsp;</td>
                    <td width="78">&nbsp;</td>
                    <td width="101">&nbsp;</td>
					<?php if ($sum_sub_total < 0) {
							$sign_string_amount = shiftLeftAndRoate($sum_sub_total); // show sign minus SAP format
						  } elseif ($sum_sub_total == 0) {
							 $sign_string_amount = "0.00";
						  } else {
							 $sign_string_amount = number_format($sum_sub_total, 2);
						  }
					?>
                    <td width="179"><strong><?php echo $sign_string_amount; ?></strong></td>
                </tr>

          </table>

    <?php 	} ?>
    <?php } ?>

<?php } // if isset($_POST["BTTexecute"] ?>

</td></tr></table>

<?php if (isset($_POST["BTTexecute"])) { ?>
    <form action="./export_gl_2_excel.php" method="post">
    <table width="100%" border="0" cellpadding="2" cellspacing="2">
        <tr>
            <td width="15%">
                <input name="BTTexport" type="submit" class="input" id="BTTexport" value="Export to EXCEL" />
                <input name="HIDdata2excel2" type="hidden" id="HIDdata2excel2" />
            </td>
            <td width="46%">&nbsp;</td>
            <td width="18%">&nbsp;</td>
            <td width="21%">&nbsp;</td>
        </tr>
    </table>
    </form>
<?php } ?>

</body>
</html>
<?php ob_end_flush(); ?>
